package cn.myschool.dao;

import cn.myschool.entity.Student;
import day26.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class StudentDao {
    public int add(Student s){
        String sql="insert into s_student(sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic)values(?,?,?,?,?,?,?,?,?,?,?,?)";
        int i =DBUtil.executeUpdate(sql,s.getSname(),s.getSex(),s.getTelephone(),s.getQq(),s.getWechat(),s.getGradeid(),s.getEnterdate(),s.getFromcity(),s.getBirthday(),s.getAddress(),s.getPass(),s.getIdcardpic());
        return i;
    }

    public static int update(Student s){
        String sql = "Update s_student set sname=?,sex=?,telephone=?,qq=?,wechat=?,gradeid=?,enterdate=?,fromcity=?,birthday=?,address=?,pass=?,idcardpic=? where stuno=?";
        int i =DBUtil.executeUpdate(sql,s.getSname(),s.getSex(),s.getTelephone(),s.getQq(),s.getWechat(),s.getGradeid(),s.getEnterdate(),s.getFromcity(),s.getBirthday(),s.getAddress(),s.getPass(),s.getIdcardpic(),s.getStuno());
        return i;
    }

    public static int del(long id){
        String sql="delete FROM  `s_student` where `stuno`=?";
        int i=DBUtil.executeUpdate(sql,id);
        return i;
    }

    //主键找对象
    public static Student get(int stuno){
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs=null;
        try {
            connection=DBUtil.getConnection();
            String sql="select * from `s_student` where `stuno`=?";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            st.setLong(1,stuno);
            rs=st.executeQuery();
            if (rs.next()){
                String sname = rs.getString(2);
                String sex = rs.getString(3);
                String telephone = rs.getString(4);
                String qq = rs.getString(5);
                String wechat = rs.getString(6);
                String gradeid = rs.getString(7);
                Date enterdate = rs.getDate(8);
                String fromcity = rs.getString(9);
                Date birthday = rs.getDate(10);
                String address = rs.getString(11);
                String pass = rs.getString(12);
                String idcardpic = rs.getString(13);
                Student student = new Student(stuno,sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic);
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(null,st,connection);
        }
        return null;
    }

    //查询全部对象
    public static List<Student> findALL(){
        Connection connection = null;
        PreparedStatement st = null;
        List<Student> students=new ArrayList<>();
        try {
            connection=DBUtil.getConnection();
            String sql="select * from `s_student` ";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            ResultSet rs=st.executeQuery();
            while (rs.next()){
                int stuno=rs.getInt(1);
                String sname = rs.getString(2);
                String sex = rs.getString(3);
                String telephone = rs.getString(4);
                String qq = rs.getString(5);
                String wechat = rs.getString(6);
                String gradeid = rs.getString(7);
                Date enterdate = rs.getDate(8);
                String fromcity = rs.getString(9);
                Date birthday = rs.getDate(10);
                String address = rs.getString(11);
                String pass = rs.getString(12);
                String idcardpic = rs.getString(13);
                Student student = new Student(stuno,sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic);
                students.add(student);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(null,st,connection);
        }
        return students;
    }

    public static List<Student> search(int pageNo,int pageSize){
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs=null;
        List<Student> students=new ArrayList<>();
        try {
            connection=DBUtil.getConnection();
            String sql="select * from `s_student` limit ?,? ;";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            st.setLong(1,(pageSize-1)*pageNo);
            st.setLong(2,pageSize);
            rs=st.executeQuery();
            while (rs.next()){
                int stuno=rs.getInt(1);
                String sname = rs.getString(2);
                String sex = rs.getString(3);
                String telephone = rs.getString(4);
                String qq = rs.getString(5);
                String wechat = rs.getString(6);
                String gradeid = rs.getString(7);
                Date enterdate = rs.getDate(8);
                String fromcity = rs.getString(9);
                Date birthday = rs.getDate(10);
                String address = rs.getString(11);
                String pass = rs.getString(12);
                String idcardpic = rs.getString(13);
                Student student = new Student(stuno,sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic);
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(null,st,connection);
        }
        return students;
    }

}
